package client;

import java.sql.*;

public class SQL {

    // MySQL 8.0 ���°汾 - JDBC �����������ݿ� URL
    //static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    //static final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB";

    // MySQL 8.0 ���ϰ汾 - JDBC �����������ݿ� URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/net?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";


    // ���ݿ���û��������룬��Ҫ�����Լ�������
    static final String USER = "root";
    static final String PASS = "123456";

    public SQL() {

    }

    public int isfri(String a, String b) {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;

            sql = "select * from user";
            ResultSet rs = stmt.executeQuery(sql);
            if (rs != null) {
                int k = 0;
                while (rs.next()) {
                    String usr = rs.getString("usr");
                    if (b.equals(usr)) {
                        k = 1;
                        break;
                    }
                }
                if (k == 0) {
                    rs.close();
                    stmt.close();
                    conn.close();
                    return 0;
                }
            }

            sql = "select * from friend where id = '" + a + "'";
            rs = stmt.executeQuery(sql);
            if (rs != null) {
                while (rs.next()) {
                    String friend = rs.getString("friend");
                    if (b.equals(friend)) {
                        rs.close();
                        stmt.close();
                        conn.close();
                        return 1;
                    }
                }
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return 2;
    }

    public int IsTrue(String usr, String pwd) {
        int key = 0;
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;
            sql = "select * from user";
            ResultSet rs = stmt.executeQuery(sql);

            // չ����������ݿ�
            if (rs != null) {
                while (rs.next()) {
                    // ͨ���ֶμ���
                    //int id  = rs.getInt("id");
                    String name = rs.getString("usr");
                    String url = rs.getString("pwd");
                    if (name.equals(usr) && url.equals(pwd)) {
                        key = 1;
                    }
                }
            }
            // ��ɺ�ر�
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }

    public int Offline(String usr) {
        int key = 0;
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;
            sql = "update user set sta = false where usr = '" + usr + "'";
            int num = stmt.executeUpdate(sql);
            if (num == 1) {
                key = 1;
            } else {
                key = 0;
            }
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }

    public int Online(String usr) {
        int key = 0;
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;

            sql = "select * from user where usr = '" + usr + "'";
            ResultSet rs = stmt.executeQuery(sql);

            // չ����������ݿ�
            if (rs != null) {
                while (rs.next()) {
                    // ͨ���ֶμ���
                    //int id  = rs.getInt("id");
                    Boolean isonline = rs.getBoolean("sta");
                    if (isonline) {
                        System.out.println("�˺�������");
                        rs.close();
                        stmt.close();
                        conn.close();
                        return key;
                    }
                }
            }


            sql = "update user set sta = true where usr = '" + usr + "'";
            int num = stmt.executeUpdate(sql);
            if (num == 1) {
                key = 1;
            } else {
                key = 0;
            }
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }

    public int Insert(String usr, String pwd) {
        int key = 1;
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;
            sql = "select * from user";
            ResultSet rs = stmt.executeQuery(sql);

            // չ����������ݿ�
            if (rs != null) {
                while (rs.next()) {
                    // ͨ���ֶμ���
                    //int id  = rs.getInt("id");
                    String name = rs.getString("usr");
                    if (name.equals(usr)) {
                        key = 0;
                        break;
                    }
                }
            }
            if (key == 1) {
                sql = "insert into user(usr, pwd, nam, sex, num, sta) values('"+usr+"','"+pwd+"','' ,'' , '', 0)";
                int num = stmt.executeUpdate(sql);
            }
            // ��ɺ�ر�
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }

    public String getpwd(String usr) {
        String key = new String("");
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;
            sql = "select * from user where usr = '" + usr + "'";
            ResultSet rs = stmt.executeQuery(sql);

            // չ����������ݿ�
            if (rs != null) {
                while (rs.next()) {
                    // ͨ���ֶμ���
                    //int id  = rs.getInt("id");
                    key = rs.getString("pwd");
                }
            }
            // ��ɺ�ر�
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }

    public String getnam(String usr) {
        String key = new String("");
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;
            sql = "select * from user where usr = '" + usr + "'";
            ResultSet rs = stmt.executeQuery(sql);

            // չ����������ݿ�
            if (rs != null) {
                while (rs.next()) {
                    // ͨ���ֶμ���
                    //int id  = rs.getInt("id");
                    key = rs.getString("nam");
                }
            }
            // ��ɺ�ر�
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }

    public String getsex(String usr) {
        String key = new String("");
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;
            sql = "select * from user where usr = '" + usr + "'";
            ResultSet rs = stmt.executeQuery(sql);

            // չ����������ݿ�
            if (rs != null) {
                while (rs.next()) {
                    // ͨ���ֶμ���
                    //int id  = rs.getInt("id");
                    key = rs.getString("sex");
                }
            }
            // ��ɺ�ر�
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }

    public String getnum(String usr) {
        String key = new String("");
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;
            sql = "select * from user where usr = '" + usr + "'";
            ResultSet rs = stmt.executeQuery(sql);

            // չ����������ݿ�
            if (rs != null) {
                while (rs.next()) {
                    // ͨ���ֶμ���
                    //int id  = rs.getInt("id");
                    key = rs.getString("num");
                }
            }
            // ��ɺ�ر�
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }

    public String getsta(String usr) {
        String key = new String("");
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;
            sql = "select * from user where usr = '" + usr + "'";
            ResultSet rs = stmt.executeQuery(sql);

            // չ����������ݿ�
            if (rs != null) {
                while (rs.next()) {
                    // ͨ���ֶμ���
                    //int id  = rs.getInt("id");
                    key = rs.getString("sta");
                }
            }
            // ��ɺ�ر�
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }


    public int setpwd(String usr, String pwd) {
        int key = 0;
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;

            // չ����������ݿ�
            sql = "update user set pwd = '" + pwd + "' where usr = '" + usr + "'";
            int num = stmt.executeUpdate(sql);
            if (num == 1) {
                key = 1;
            } else {
                key = 0;
            }
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }


    public int setnam(String usr, String nam) {
        int key = 0;
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;

            // չ����������ݿ�
            sql = "update user set nam = '" + nam + "' where usr = '" + usr + "'";
            int num = stmt.executeUpdate(sql);
            if (num == 1) {
                key = 1;
            } else {
                key = 0;
            }
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }


    public int setsex(String usr, String sex) {
        int key = 0;
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;

            // չ����������ݿ�
            sql = "update user set sex = '" + sex + "' where usr = '" + usr + "'";
            int num = stmt.executeUpdate(sql);
            if (num == 1) {
                key = 1;
            } else {
                key = 0;
            }
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }


    public int setnum(String usr, String num1) {
        int key = 0;
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;

            // չ����������ݿ�
            sql = "update user set num = '" + num1 + "' where usr = '" + usr + "'";
            int num = stmt.executeUpdate(sql);
            if (num == 1) {
                key = 1;
            } else {
                key = 0;
            }
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
        return key;
    }


    public void getfri(String usr, String[] frilist) {
        Connection conn = null;
        Statement stmt = null;
        try {
            // ע�� JDBC ����
            Class.forName(JDBC_DRIVER);

            // ������
            //System.out.println("�������ݿ�...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // ִ�в�ѯ
            //System.out.println(" ʵ����Statement����...");
            stmt = conn.createStatement();
            String sql;

            sql = "select * from friend where id = '" + usr + "'";
            ResultSet rs = stmt.executeQuery(sql);

            // չ����������ݿ�
            if (rs != null) {
                int i = 0;
                while (rs.next()) {
                    // ͨ���ֶμ���
                    //int id  = rs.getInt("id");
                    frilist[i] = rs.getString("friend");
                    i++;
                }
            }
            // ��ɺ�ر�
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // ���� JDBC ����
            se.printStackTrace();
        } catch (Exception e) {
            // ���� Class.forName ����
            e.printStackTrace();
        } finally {
            // �ر���Դ
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// ʲô������
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        //System.out.println("Goodbye!");
    }

    public Boolean addfri(String id, String fri) {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;
            sql = "insert into friend(id, friend) values('" + id + "','" + fri + "')";
            int num = stmt.executeUpdate(sql);
            if (num == 0) {
                stmt.close();
                conn.close();
                return false;
            }
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return true;
    }

    public static void main(String[] args) {
        SQL a = new SQL();
        String b = new String("qyx");
        String c = new String("123456");
        int k = a.IsTrue(b, c);
        System.out.println(k);
    }

}